package com.mavenq.fly.test.jsqlparser;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.*;

import java.io.StringReader;

public class NestedSqlModification {
    public static void main(String[] args) {
        String sql = "SELECT u.id, o.order_id " +
                     "FROM users u " +
                     "JOIN (SELECT user_id, order_id FROM orders WHERE status = 'active') o " +
                     "ON u.id = o.user_id " +
                     "WHERE u.age > 25";
        System.out.println("修改前的 SQL：");
        System.out.println(sql);
        try {
            // 1. 初始化解析器
            CCJSqlParserManager parserManager = new CCJSqlParserManager();
            Statement statement = parserManager.parse(new StringReader(sql));
            
            if (statement instanceof Select) {
                Select select = (Select) statement;
                PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
                
                // 2. 修改 JOIN 中的子查询表名
                for (Join join : plainSelect.getJoins()) {
                    FromItem rightItem = join.getRightItem();
                    if (rightItem instanceof SubSelect) {
                        SubSelect subSelect = (SubSelect) rightItem;
                        // 替换子查询的表名
                        subSelect.setSelectBody(modifySubSelectTable(subSelect));
                    }
                }
                
                // 3. 修改主查询的 WHERE 条件
                Expression originalWhere = plainSelect.getWhere();
                Expression newWhere = CCJSqlParserUtil.parseCondExpression(
                    originalWhere.toString() + " AND u.status = 'VIP'"
                );
                plainSelect.setWhere(newWhere);
                
                // 4. 输出修改后的 SQL
                System.out.println("修改后的 SQL：");
                System.out.println(select.toString());
            }
        } catch (JSQLParserException e) {
            e.printStackTrace();
        }
    }

    // 修改子查询的 FROM 表名
    private static SelectBody modifySubSelectTable(SubSelect subSelect) {
        PlainSelect subPlainSelect = (PlainSelect) subSelect.getSelectBody();
        // 替换子查询的表名
        subPlainSelect.setFromItem(new Table("payments"));
        return subPlainSelect;
    }
}